package org.boot.fast.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import static java.sql.Types.BOOLEAN;
import static java.sql.Types.NUMERIC;

/**
 * 通用导入Excel工具
 * @author: 最爱芫荽
 * @email: 15733440205@163.com
 */
@Slf4j
@SuppressWarnings("all")
public class ExcelImportUtil {

    /**
     * 通用 Excel 导入
     * 支持 .xls 与 .xlsx
     * 默认读取第 1 个 sheet，首行为表头
     *
     * @param file MultipartFile
     * @return List<Map < 列名, 单元格值>>
     */
    public static List<Map<String, Object>> analysisExcel(MultipartFile file) {
        List<Map<String, Object>> result = new ArrayList<>();
        if (file == null || file.isEmpty()) {
            log.warn("上传文件为空");
            return result;
        }

        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null ||
                (!originalFilename.endsWith(".xls") && !originalFilename.endsWith(".xlsx"))) {
            log.error("文件格式非法，仅支持 xls/xlsx");
            return result;
        }

        try (InputStream is = file.getInputStream()) {
            Workbook workbook = originalFilename.endsWith(".xlsx") ?
                    new XSSFWorkbook(is) : new HSSFWorkbook(is);

            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();

            // 读取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                log.warn("首行表头缺失");
                return result;
            }
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getStringValue(cell));
            }

            // 读取数据
            for (int i = 1; i <= lastRowNum; i++) {
                Row dataRow = sheet.getRow(i);
                if (dataRow == null) continue;   // 空行跳过
                Map<String, Object> rowMap = new LinkedHashMap<>(headers.size());
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = dataRow.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    rowMap.put(headers.get(j), getRealValue(cell));
                }
                result.add(rowMap);
            }

            log.info("Excel 解析完成，共 {} 条数据", result.size());
        } catch (IOException e) {
            log.error("解析 Excel 异常", e);
        }
        return result;
    }


    /**
     * 通用 Excel 导入
     * 支持 .xls 与 .xlsx
     * 默认读取第 1 个 sheet，首行为表头
     * 列名映射为数据库字段名
     * @param file MultipartFile
     * @return List<Map < 列名, 单元格值>>
     */
    public static List<Map<String, Object>> analysisExcel(MultipartFile file, Map<String, Object> configMapping) {
        List<Map<String, Object>> result = new ArrayList<>();
        if (file == null || file.isEmpty()) {
            throw new RuntimeException("Excel 解析异常：上传文件为空");
        }

        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null ||
                (!originalFilename.endsWith(".xls") && !originalFilename.endsWith(".xlsx"))) {
            throw new RuntimeException("Excel 解析异常：文件格式非法，仅支持 xls/xlsx");
        }

        try (InputStream is = file.getInputStream()) {
            Workbook workbook = originalFilename.endsWith(".xlsx") ?
                    new XSSFWorkbook(is) : new HSSFWorkbook(is);

            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();

            // 读取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new RuntimeException("Excel 解析异常：表头缺失");
            }
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getStringValue(cell));
            }

            checkExcelConfigJson(headers, configMapping);
            // 读取数据
            for (int i = 1; i <= lastRowNum; i++) {
                Row dataRow = sheet.getRow(i);
                if (dataRow == null) continue;   // 空行跳过
                Map<String, Object> rowMap = new LinkedHashMap<>(headers.size());
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = dataRow.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    String key = headers.get(j);
                    Object obj = configMapping.get(headers.get(j));
                    if(obj != null) key = obj.toString();
                    rowMap.put(key, getRealValue(cell));
                }
                result.add(rowMap);
            }

            log.info("Excel 解析完成，共 {} 条数据", result.size());
        } catch (IOException e) {
            throw new RuntimeException("解析 Excel 异常：" + e.getMessage());
        }
        return result;
    }



    /* ======================== 内部工具方法 ======================== */

    /**
     * 统一转字符串（表头用）
     */
    private static String getStringValue(Cell cell) {
        if (cell == null) return "";
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    /**
     * 智能解析单元格真实类型
     * Numeric -> 返回 Double / Date
     * Boolean -> Boolean
     * 其余 -> String
     */
    private static Object getRealValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
                return cell.getNumericCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            default:
                cell.setCellType(CellType.STRING);
                String val = cell.getStringCellValue();
                return val == null || val.trim().isEmpty() ? null : val.trim();
        }
    }


    private static void checkExcelConfigJson(List<String> headers, Map<String, Object> configMapping) {
        if (configMapping == null || configMapping.isEmpty()) {
            log.error("Excel 导入配置不能为空");
            throw new IllegalArgumentException("Excel 导入配置不能为空");
        }
    }
}
